package com.ce.pms.occupation.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ce.pms.base.service.impl.PmsBaseServiceImpl;
import com.ce.pms.charge.vo.ImportErrorOrWarnVO;
import com.ce.pms.common.dao.IOrganizationalStructureDao;
import com.ce.pms.common.model.HmEmployeeDomain;
import com.ce.pms.exception.BusinessException;
import com.ce.pms.occupation.dao.ICompletePerfectItemDao;
import com.ce.pms.occupation.dao.IPerfectItemDispatchDao;
import com.ce.pms.occupation.dao.IPerfectItemRequisitionDao;
import com.ce.pms.occupation.dao.IPerfectItemTrackDao;
import com.ce.pms.occupation.service.IDispatchExportService;
import com.ce.pms.occupation.service.IDispatchImportService;
import com.ce.util.BlankUtil;
import com.ce.util.DateUtil;
/**
 * 功能描述: 入伙派工导入类
 * <p>版权所有：中企动力
 * <p>未经本公司许可，不得以任何方式复制或使用本程序任何部分
 *
 * @author  胡煦
 * Create on:2009-1-5
 * Edit author:
 * Edit on:
 * 修改原因：

 */

public class DispatchImportServiceImpl extends PmsBaseServiceImpl implements
		IDispatchImportService {
	/**
	 * 方法描述: 验证excel文件,
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param workbook，perfectItemDispatchId
	 *
	 * @return Map <参数类型，参数集合> Edit author: Edit on: 修改原因：
	 */
	public Map checkTheExcel(Long perfectItemDispatchId, HSSFWorkbook workbook) {
		dispatchExportService.checkDispatchIsComp(perfectItemDispatchId);
		this.checkExcelFileHead(workbook);
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		List<ImportErrorOrWarnVO> warnVOList = new ArrayList<ImportErrorOrWarnVO>();
		Map<String, List> map = new HashMap<String, List>();
		Date recordTime = perfectItemDispatchDao.findById(perfectItemDispatchId).getDispatchDate();
		//存放导入数据字符串行的集合，用于判断是否有相同记录
		List<String> composeDataList = this.getComposeData(perfectItemDispatchId);
		for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++)

		{

			if (null != workbook.getSheetAt(numSheets))

			{
				// 获得一个sheet
				HSSFSheet aSheet = workbook.getSheetAt(numSheets);

				for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet
						.getLastRowNum(); rowNumOfSheet++)

				{
					// 获取错误参数集合
					errorVOList
							.addAll(this.checkTheErrorData(aSheet, rowNumOfSheet, composeDataList, recordTime));
					// 获取警告参数集合
					warnVOList.addAll(this.checkTheWarnData(aSheet,
							rowNumOfSheet));
				}
			}

		}
		map.put("error", errorVOList);
		map.put("warn", warnVOList);
		return map;
	}

	/**
	 * 方法描述: 批量读取保存excel中记录
	 *
	 * @author 胡煦 Create on:2008-8-21
	 * @param HSSFWorkbook
	 *            workbook,Long perfectItemDispatchId,Long creatorId
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	public boolean readExcelAndSave(HSSFWorkbook workbook,Long perfectItemDispatchId,Long creatorId) {

		HmEmployeeDomain employeeDomain = organizationalStructureDao.queryUserByUserId(creatorId) ;
		List<Object[]> list = this.getExcelList(workbook);
		List<Object[]> listForTracks = null;
		List<Object[]> listForCompPerItems = null;
		List<Object[]> listForRequisitions = null;
		if(!BlankUtil.isBlank(list)){
			listForTracks = new ArrayList<Object[]>();
			listForCompPerItems = new ArrayList<Object[]>();
			listForRequisitions = new ArrayList<Object[]>();

			for(Object[] objects : list){
				Object[] objOfTracks = new Object[]{objects[0],employeeDomain.getEmployeeId(),objects[1].toString()};
				Object[] objOfCompPerItems = new Object[]{perfectItemDispatchId,objects[0],employeeDomain.getEmployeeId(),objects[1].toString()};
				Object[] objOfRequisitions = new Object[]{objects[0]};
				listForTracks.add(objOfTracks);
				listForCompPerItems.add(objOfCompPerItems);
				listForRequisitions.add(objOfRequisitions);
			}
		}
		if(!BlankUtil.isBlank(listForRequisitions)){
			perfectItemRequisitionDao.batchUpadateDispatchs(listForRequisitions);
		}
		if(!BlankUtil.isBlank(listForTracks)){
			perfectItemTrackDao.batchInsertPerfectItemTracks(listForTracks);
		}
		if(!BlankUtil.isBlank(listForCompPerItems)){
			completePerfectItemDao.batchInsertCompPerItems(listForCompPerItems);
		}
		return true;
	}

	/**
	 * 方法描述: 创建工作簿,用来和导入的EXCEL进行比对校验
	 *
	 * @author 胡煦 Create on:2008-8-26
	 * @param Long
	 *            perfectItemDispatchId
	 * @return HSSFWorkbook Edit author: Edit on: 修改原因：
	 */
	private HSSFWorkbook createHSSFWorkbook(Long perfectItemDispatchId) {
		return dispatchExportService.createHSSFWorkbook(perfectItemDispatchId);
	}

	/**
	 * 方法描述: 验证整个EXCEL的文件头
	 *
	 * @author 胡煦 Create on:2008-10-16
	 * @param nameStr：查询条件班级名称
	 * @return 返回当前页的查询结果 Edit author: Edit on: 修改原因：
	 */
	private void checkExcelFileHead(HSSFWorkbook workbook) {
		for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
			if(workbook.getSheetAt(numSheets).getLastRowNum() > 0){
				this.checkSheetFileHead(workbook.getSheetAt(numSheets));
			}
		}
	}

	/**
	 * 方法描述: 获取从数据库获得的组合数据
	 *
	 * @author 胡煦 Create on:2008-8-26
	 * @param Long
	 *            buildingId
	 * @return HSSFWorkbook Edit author: Edit on: 修改原因：
	 */
	private List<String> getComposeData(Long perfectItemDispatchId) {

		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();

			HSSFWorkbook workbook = createHSSFWorkbook(perfectItemDispatchId);
			List<String> list = new ArrayList();
			for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++)

			{

				if (null != workbook.getSheetAt(numSheets))

				{
					// 获得一个sheet
					HSSFSheet aSheet = workbook.getSheetAt(numSheets);
					for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet
							.getLastRowNum(); rowNumOfSheet++)

					{
						// 获取Sheet中的某一行
						HSSFRow aRow = aSheet.getRow(rowNumOfSheet);

						// 定义导入数据字符串行，用于判断项目编号、房间号码、项目类型的组合是否与文件中的一致，校验其是否人工修改其相关的字段值
						String str = aRow.getCell((short) 0)
								.getNumericCellValue()
								+ aRow.getCell((short) 1).getStringCellValue()
										.trim()
								+ aRow.getCell((short) 2).getStringCellValue().trim();
						// 放入集合
						list.add(str);
					}
				}
			}

		return list;
	}

	/**
	 * 方法描述: 判断项目编号、房间号码、项目类型的组合是否正确
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param
	 * @return List<ImportErrorOrWarnVO> Edit author: Edit on: 修改原因：
	 */
	private ImportErrorOrWarnVO checkComposeData(List<String> list, HSSFRow aRow,
			 int rowNumOfSheet) {

		// 定义导入数据字符串行，用于判断是否有相同记录
		String str = aRow.getCell((short) 0).getNumericCellValue()
				+ aRow.getCell((short) 1).getStringCellValue().trim()
				+ aRow.getCell((short) 2).getStringCellValue().trim();

		// 判断EXCEL中是否有相同记录
		if (BlankUtil.isBlank(compareInList(str, list))) {
			ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
			errorVO.setRowNumber(rowNumOfSheet + 1);
			errorVO.setParticularInfo("项目编号、房号、项目类型的组合不正确");
			return errorVO;
		}
		return null;
	}

	/**
	 * 方法描述: 验证集合中是否存在某值
	 *
	 * @author 胡煦 Create on:2008-9-10
	 * @param String
	 *            str,List<String> list
	 * @return String Edit author: Edit on: 修改原因：
	 */
	private String compareInList(String str, List<String> list) {

		for (String string : list) {
			if (string.equals(str)) {
				return string;
			}
		}
		return null;
	}

	/**
	 * 方法描述: 验证错误数据
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param nameStr：
	 * @return List<ImportErrorOrWarnVO> Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkTheErrorData(
			HSSFSheet aSheet, int rowNumOfSheet,
			List<String> list, Date recordTime) {
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		// 获取Sheet中的某一行
		HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
		if(!BlankUtil.isBlank(aRow)){
			// 验证EXCEL文件插入字段是否合法
			errorVOList.addAll(checkExcelFieldIsTrue(aRow, rowNumOfSheet,
					 recordTime));
			// 验证单元格是否为空
			errorVOList
					.addAll(checkAllExcelCellNull(aRow, rowNumOfSheet));
			if (BlankUtil.isBlank(errorVOList)) {
				// 查询其数据库中的楼宇，楼层和底数是否与文件中的一致，校验其是否人工修改其相关的字段值
				if (!BlankUtil.isBlank(checkComposeData(list, aRow, rowNumOfSheet))) {
					errorVOList.add(checkComposeData(list, aRow, rowNumOfSheet));
				}
			}
		}
		return errorVOList;
	}

	/**
	 * 方法描述: 验证警告数据
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param workbook
	 * @return 返回当前页的查询结果 Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkTheWarnData(HSSFSheet aSheet,int rowNumOfSheet) {
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		// 获取Sheet中的某一行
		HSSFRow row = aSheet.getRow(rowNumOfSheet);
		if(!BlankUtil.isBlank(row)){
			// 验证完工日期是否存在
			if ((BlankUtil.isBlank((row.getCell((short) 4)))|| row.getCell((short) 4).getCellType() == HSSFCell.CELL_TYPE_BLANK)) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setParticularInfo("完工日期未填写，将不予导入");
				errorVOList.add(errorVO);
			}
		}
		return errorVOList;
	}

	/**
	 * 方法描述: 读取保存excel中记录
	 *
	 * @author 胡煦 Create on:2008-8-21
	 * @param sourcefile
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private List<Object[]> getExcelList(HSSFWorkbook workbook) {

		List<Object[]> list = new ArrayList<Object[]>();
		for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++)

		{

			if (0 != workbook.getSheetAt(numSheets).getLastRowNum())

			{
				// 获得一个sheet
				HSSFSheet aSheet = workbook.getSheetAt(numSheets);

				for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet
						.getLastRowNum(); rowNumOfSheet++)

				{

					if (null != aSheet.getRow(rowNumOfSheet))

					{
						// 获取Sheet中的某一行
						HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
						if(!BlankUtil.isBlank(aRow)){
							if (!BlankUtil.isBlank(aRow.getCell((short) 4))
									&& aRow.getCell((short) 4).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
								// 设置完工日期
								Date recordTime = null;
								String dateStr = null;
								if(aRow.getCell((short) 4).getCellType() != HSSFCell.CELL_TYPE_NUMERIC){
									recordTime = DateUtil.convertStrToDate(aRow.getCell((short) 4)
											.toString(), "yyyy-MM-dd");
								}else{
									recordTime = HSSFDateUtil.getJavaDate(aRow.getCell((short) 4).getNumericCellValue());
									dateStr = DateUtil.convertDateToStr(recordTime, "yyyy-MM-dd");
								}

								Object[] objects = new Object[] { aRow.getCell((short) 0).getNumericCellValue(), dateStr};
								// 获取集合
								list.add(objects);
							}

						}

					}

				}

			}

		}
		return list;
	}


	/**
	 * 方法描述: 验证EXCEL单元格是否为空
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param cell，row ,
	 *            column
	 * @return void Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkAllExcelCellNull(HSSFRow aRow,
			int rowNumOfSheet) {
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		for (short cellNumOfRow = 0; cellNumOfRow < 3; cellNumOfRow++)

		{
			HSSFCell aCell = aRow.getCell(cellNumOfRow);
			// 验证某个单元格是否为空
			if (BlankUtil.isBlank(aCell)
					|| aCell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				short NumOfRow = (short) (cellNumOfRow + 1);
				errorVO.setParticularInfo("第" + NumOfRow + "列单元格不能为空");
				errorVOList.add(errorVO);
			}
		}
		return errorVOList;
	}

	/**
	 * 方法描述: 验证EXCEL表头文件单元格
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param sheet
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private void checkExcelHeadCellLike(HSSFCell cell, String cellName, int column) {

		if (BlankUtil.isBlank(cell)||!cellName.equalsIgnoreCase(cell.getStringCellValue().trim())) {
			throw new BusinessException("charge:excel_cell_error_exception,"
					+ column + ":" + cellName, BusinessException.WARNING);
		}
	}

	/**
	 * 方法描述: 验证EXCEL文件表头格式
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param sheet
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private boolean checkSheetFileHead(HSSFSheet sheet) {

		HSSFRow row = null;

		HSSFCell cell = null;

		row = sheet.getRow(0);
		// 验证第一列为项目编号且不为空
		cell = row.getCell((short) 0);

		this.checkExcelHeadCellLike(cell, "项目编号", 1);
		// 验证第二列为房号且不为空
		cell = row.getCell((short) 1);

		this.checkExcelHeadCellLike(cell, "房号", 2);
		// 验证第三列为项目类型且不为空
		cell = row.getCell((short) 2);


		this.checkExcelHeadCellLike(cell, "项目类型", 3);
		// 验证第四列为问题描述且不为空
		cell = row.getCell((short) 3);


		this.checkExcelHeadCellLike(cell, "问题描述", 4);
		// 验证第五列为完工日期且不为空
		cell = row.getCell((short) 4);


		this.checkExcelHeadCellLike(cell, "完工日期", 5);
		// 验证第六列为备注且不为空
		cell = row.getCell((short) 5);


		this.checkExcelHeadCellLike(cell, "备注", 6);

		return true;
	}

	/**
	 * 方法描述: 判断数组中是否有重复值
	 *
	 * @author 胡煦 Create on:2008-8-22
	 * @param String []
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private boolean judgeStrRepeat(Object[] str) {
		Set set = new HashSet();

		for (int i = 0, n = str.length; i < n; i++)

		{
			if (!BlankUtil.isBlank(str[i])) {
				if (set.contains(str[i]))

				{
					return false;

				}
			}
			set.add(str[i]);

		}
		return true;
	}

	/**
	 * 方法描述: 验证EXCEL文件插入字段是否合法
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param sheet
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkExcelFieldIsTrue(HSSFRow row,
			int rowNumOfSheet, Date recordTime) {
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();

		if (!BlankUtil.isBlank(row.getCell((short) 0))
				&& row.getCell((short) 0).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
			// 验证项目编号是否是数字型
			Pattern pattern = Pattern.compile("^\\d+(\\.\\d+)?$");
			Matcher matcher = pattern.matcher(row.getCell((short) 0).toString());
			boolean a = matcher.matches();
			if (!a) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setParticularInfo("项目编号必须为数字且不为空");
				errorVOList.add(errorVO);
			}
		}

		// 验证完工日期格式是否正确
		if (!BlankUtil.isBlank(row.getCell((short) 4))
				&& row.getCell((short) 4).getCellType() != HSSFCell.CELL_TYPE_BLANK) {

			Pattern pattern = Pattern.compile("^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))");
			Matcher matcher = null;
			if(row.getCell((short) 4).getCellType() != HSSFCell.CELL_TYPE_NUMERIC){
				matcher = pattern.matcher(row.getCell((short) 4).toString());
			}else{
				String dateStr = DateUtil.convertDateToStr(row.getCell((short) 4).getDateCellValue(), "yyyy-MM-dd");
				matcher = pattern.matcher(dateStr);
			}

			boolean a = matcher.matches();
			if (!a) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setParticularInfo("完工日期时间格式不正确");
				errorVOList.add(errorVO);
			} else {
				Date date = null;
				if(row.getCell((short) 4).getCellType() != HSSFCell.CELL_TYPE_NUMERIC){
					date = DateUtil.convertStrToDate(row.getCell((short) 4)
							.toString(), "yyyy-MM-dd");
				}else{
					date = HSSFDateUtil.getJavaDate(row.getCell((short) 4).getNumericCellValue());
				}
				// 验证本次抄表时间必须大于等于数据库中最大的抄表时间
				if (!BlankUtil.isBlank(recordTime)) {
					if (recordTime.compareTo(date) == 1) {
						ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
						errorVO.setRowNumber(rowNumOfSheet + 1);
						errorVO.setParticularInfo("完工日期不能小于派工日期！");
						errorVOList.add(errorVO);
					}
				}
			}
		}
		return errorVOList;
	}
	private IOrganizationalStructureDao organizationalStructureDao;
	private IDispatchExportService dispatchExportService;
	private IPerfectItemDispatchDao perfectItemDispatchDao;
	private IPerfectItemRequisitionDao perfectItemRequisitionDao;
	private ICompletePerfectItemDao completePerfectItemDao;
	private IPerfectItemTrackDao perfectItemTrackDao;
	/**
	 * @param organizationalStructureDao the organizationalStructureDao to set
	 */
	public void setOrganizationalStructureDao(
			IOrganizationalStructureDao organizationalStructureDao) {
		this.organizationalStructureDao = organizationalStructureDao;
	}

	/**
	 * @param dispatchExportService the dispatchExportService to set
	 */
	public void setDispatchExportService(
			IDispatchExportService dispatchExportService) {
		this.dispatchExportService = dispatchExportService;
	}

	/**
	 * @param perfectItemDispatchDao the perfectItemDispatchDao to set
	 */
	public void setPerfectItemDispatchDao(
			IPerfectItemDispatchDao perfectItemDispatchDao) {
		this.perfectItemDispatchDao = perfectItemDispatchDao;
	}

	/**
	 * @param completePerfectItemDao the completePerfectItemDao to set
	 */
	public void setCompletePerfectItemDao(
			ICompletePerfectItemDao completePerfectItemDao) {
		this.completePerfectItemDao = completePerfectItemDao;
	}

	/**
	 * @param perfectItemRequisitionDao the perfectItemRequisitionDao to set
	 */
	public void setPerfectItemRequisitionDao(
			IPerfectItemRequisitionDao perfectItemRequisitionDao) {
		this.perfectItemRequisitionDao = perfectItemRequisitionDao;
	}

	/**
	 * @param perfectItemTrackDao the perfectItemTrackDao to set
	 */
	public void setPerfectItemTrackDao(IPerfectItemTrackDao perfectItemTrackDao) {
		this.perfectItemTrackDao = perfectItemTrackDao;
	}
}
